package com.egame.manage.biz.server.gc;
import java.io.DataOutputStream;
import java.net.URL;
import java.net.URLConnection;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Node;

import cn.egame.common.data.BaseDao;
import cn.egame.common.data.SqlUtils;
import cn.egame.common.exception.ExceptionCommonBase;
import cn.egame.common.util.Utils;
import cn.egame.interfaces.ConstVar;
import cn.egame.interfaces.EnumType;
import cn.egame.interfaces.EnumType.GameStatus;
import cn.egame.interfaces.fl.FileInfo;
import cn.egame.interfaces.fl.FileUsedType;
import cn.egame.interfaces.fl.FileUtils;
import cn.egame.interfaces.gc.GameInfo;
import com.egame.manage.biz.server.ManageClient;
import com.egame.manage.biz.domain.ImageInfo;

public class GameServiceDao extends BaseDao {
	private final static String STR_SELECT_GAME_INFO = " t1.g_id, t1.game_type, t1.game_code, t1.game_name, t1.network_type,"
			+ " t1.os_code, t1.pay_code, t1.fee_code, t1.is_inpackage, t1.game_status, t1.is_sns_game,"
			+ " t1.sns_position, t1.effective_time, t1.publish_time, t1.first_online_time, t1.last_online_time, t1.last_offline_time,"
			+ " t1.is_mouse, t1.is_keyboard, t1.is_remote_control, t1.is_handle, t1.is_sense, t1.operator_id,"
			+ " t1.insert_time, t1.update_time, t1.cp_id, t1.game_label, t1.sms_address, t1.payment_address,"
			+ " t1.strategy, t1.introduction, t1.copyright_path, t1.operator_name, t1.game_demo_path, t1.other_operator,t1.tcl_app_id,"
			+ " t1.price ";
	
	public GameServiceDao() throws ExceptionCommonBase {
		super("egame_game");
	}
	private GameInfo formatGameInfo(ResultSet rs) throws ExceptionCommonBase {
		GameInfo item = new GameInfo();
		try {
			int i = 1;
			item.setGId(rs.getInt(i++));
			item.setGameType(EnumType.GameType.lookup(rs.getInt(i++)));
			item.setGameCode(rs.getString(i++));
			item.setGameName(rs.getString(i++));
			item.setNetworkType(EnumType.NetworkType.lookup(rs.getInt(i++)));

			item.setOsCode(rs.getInt(i++));
			item.setPayCode(rs.getInt(i++));
			item.setFeeCode(rs.getInt(i++));
			item.setIsInpackage(rs.getInt(i++));

			item.setGameStatus(EnumType.GameStatus.lookup(rs.getInt(i++)));
			item.setIsSnsGame(rs.getInt(i++));
			item.setSnsPosition(rs.getInt(i++));
			item.setEffectiveTime(Utils.toUtilDate(rs.getTimestamp(i++)));
			item.setPublishTime(Utils.toUtilDate(rs.getTimestamp(i++)));

			item.setFirstOnlineTime(rs.getLong(i++));
			item.setLastOnlineTime(Utils.toUtilDate(rs.getTimestamp(i++)));
			item.setLastOfflineTime(Utils.toUtilDate(rs.getTimestamp(i++)));
			item.setIsMouse(rs.getInt(i++));
			item.setIsKeyboard(rs.getInt(i++));

			item.setIsRemoteControl(rs.getInt(i++));
			item.setIsHandle(rs.getInt(i++));
			item.setIsSense(rs.getInt(i++));
			item.setOperatrId(rs.getLong(i++));
			item.setInsertTime(Utils.toUtilDate(rs.getTimestamp(i++)));

			item.setUpdateTime(Utils.toUtilDate(rs.getTimestamp(i++)));
			item.setCPId(rs.getInt(i++));
			item.setGameLabel(rs.getString(i++));
			item.setSmsAddress(rs.getString(i++));
			item.setPaymentAddress(rs.getString(i++));
			item.setStrategy(rs.getString(i++));

			item.setIntroduction(rs.getString(i++));
			item.setCopyrightPath(rs.getString(i++));
			item.setOperatorName(rs.getString(i++));
			item.setGameDemoPath(rs.getString(i++));
			item.setOtherOperator(rs.getString(i++));

			item.setTclAppId(rs.getString(i++));
			item.setPrice(rs.getInt(i++));
		} catch (SQLException e) {
			throw ExceptionCommonBase.throwExceptionCommonBase(e);
		}
		
		return item;
	}

	public List<GameInfo> listGameInfoByIds(List<Integer> gids)
			throws ExceptionCommonBase {
		List<GameInfo> list = new ArrayList<GameInfo>();
		if(gids != null && gids.size()>0){
			String sql = "SELECT " + STR_SELECT_GAME_INFO
					+ " FROM t_game t1" 
					+ " where t1.g_id in (" + Utils.toString(gids) + ")";
			Connection conn = this.getConnection();
			ResultSet rs = this.executeQuery(conn, sql);
			try {
				
				while (rs.next()) {
					list.add(formatGameInfo(rs));
				}
				return list;
			} catch (SQLException e) {
				throw ExceptionCommonBase.throwExceptionCommonBase(e);
			} finally {
				this.close(rs);
				this.releaseConnection(conn);
			}
		}
		return list;
	}
	public int getGameIdByGameCode(String gameCode) throws ExceptionCommonBase {
		String sql = "select g_id from t_game where game_code="
				+ SqlUtils.QuataStr(gameCode);
		return this.getInt(sql);
	}

	public GameInfo getGameInfoById(long gid) throws ExceptionCommonBase {
		String sql = "SELECT " + STR_SELECT_GAME_INFO
				+ " FROM t_game t1 where t1.g_id = " + gid;
		Connection conn = this.getConnection();
		ResultSet rs = this.executeQuery(conn, sql);
		try {
			if (rs.next()) {
				return formatGameInfo(rs);
			}
		} catch (SQLException e) {
			throw ExceptionCommonBase.throwExceptionCommonBase(e);
		} finally {
			this.close(rs);
			this.releaseConnection(conn);
		}
		return null;
	}
	public List<Integer> listGameList(int appId,Long loginUserId,GameInfo bean,String shortName, int page,int rowsOfPage)throws RemoteException{		
		String sql = "select g_id from t_game where 1=1 ";
		if(bean != null){
			if(bean.getGameStatus().value()>=0){
				sql += " and game_status = " + bean.getGameStatus().value();
			}
			if(bean.getNetworkType().value()>0){
				sql += " and network_type = " + bean.getNetworkType().value();
			}
			if(bean.getGameType()!=null){
				sql += " and game_type = " + bean.getGameType().value();
			}
			if(!Utils.stringIsNullOrEmpty(bean.getGameName())){
				sql += "  and game_name like " + SqlUtils.QuataLikeStr(bean.getGameName().trim());
			}
			if(!Utils.stringIsNullOrEmpty(shortName)){
				sql += " and cp_id in ( SELECT cp_id from t_content_provider where short_name like " + SqlUtils.QuataLikeStr(shortName) + ")";
			}
		}
		
		sql += " limit " + page * rowsOfPage + "," + rowsOfPage;
		
		System.out.println(sql);
		
		return this.getListInteger(sql);
	}
	

	public List<Integer> listGameList(GameInfo bean)throws ExceptionCommonBase{		
		String sql = "select g_id from t_game where 1=1";
		if(bean != null){			
			if(bean.getGameName().trim().length()>0){
				sql += "  and game_name like " + SqlUtils.QuataLikeStr(bean.getGameName());
			}
			if(bean.getGameStatus() != null && bean.getGameStatus() != GameStatus.none){
				sql += "  and game_status = " + bean.getGameStatus().value();
			}
		}
				
		System.out.println(sql);
		
		return this.getListInteger(sql);
	}
	
	public int getGameCount(List<Integer> statusList) throws ExceptionCommonBase{
		String sql = "select count(*) from t_game ";
		if(statusList != null && statusList.size()>0){
			sql += " where game_status in (" + Utils.toString(statusList) + ")";
		}		
		return this.getInt(sql);
	}
	
	public int updateGameLabel(int gId, String label)throws ExceptionCommonBase{
		String sql = "update t_game set game_label = " + SqlUtils.QuataStr(label) + " where g_id =" + gId;
		return this.executeUpdate(sql);
	}
	
	public int updateGameStatus(String[] gIds,int gameStatus)throws ExceptionCommonBase{
		String sql = "update t_game set game_status = " + gameStatus + " where g_id in(" + Utils.toString(gIds) + ")";
		return this.executeUpdate(sql);
	}
	
	public int setGameImage(long loginUserId,long efsId,int gameId)throws ExceptionCommonBase{
		String sql = "insert into t_game_image(efs_id, g_id, operator_id, insert_time)values("
				+ efsId + ","
				+ gameId + ","
				+ loginUserId + ", now())";
		return this.executeUpdate(sql);
	}
	
	public Map<String, Object> listCP()throws ExceptionCommonBase{
		String sql = "select cp_id,short_name from t_content_provider where cp_status = 1";
		List<Map<String, Object>> maps = this.getListKeyValue(sql);
		Map<String, Object> result = new HashMap<String, Object>();
		for(Map<String, Object> map:maps){
			result.putAll(map);
		}
		return result;
	}
	
	public Map<String,Object> listCpIdAndName(String shortName)throws ExceptionCommonBase{
		String sql="select cp_id,short_name from t_content_provider where 1=1 ";
		if(!Utils.stringIsNullOrEmpty(shortName)){
			sql += " and short_name like " +SqlUtils.QuataLikeStr(shortName.trim());
		}
		System.out.println(sql);
		List<Map<String,Object>> maps= this.getListKeyValue(sql);
		Map<String,Object> result = new HashMap<String,Object>();
		for(Map<String,Object> map:maps) {
			result.putAll(map);
		}
		return result;
	}

	public String generateGameCode()throws ExceptionCommonBase{
		String sql = "select MAX(game_code) from t_game where game_code < 70400000000";
		System.out.println(sql);
		return String.valueOf((this.getLong(sql) + 1));
	}
	
	public List<String[]> getGameNameAndLabel() throws ExceptionCommonBase{
		String sql = "select g_id,game_name,game_label from t_game order by g_id asc";
		return this.getListStrings(sql);		
	}
	
	public int updateGameTime(String timeName,int gId,Date date) throws ExceptionCommonBase{
		if(!Utils.stringIsNullOrEmpty(timeName) && date != null){
			String formatDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(date);
			String sql = "update t_game set " + timeName + " = " + SqlUtils.QuataStr(formatDate) + " where g_id = " + gId;
			System.out.println(sql);
			return this.executeUpdate(sql);
		}
		return -1;
	}

	public int updateGameTime(String timeName,int gId,long time) throws ExceptionCommonBase{
		if(!Utils.stringIsNullOrEmpty(timeName)){
			String sql = "update t_game set " + timeName + " = " + time + " where g_id = " + gId;
			System.out.println(sql);
			return this.executeUpdate(sql);
		}
		return -1;
	}

	public int getGameCount(GameInfo bean, String shortName) throws ExceptionCommonBase {
		String sql = "select count(g_id) from t_game where 1=1 ";
		if(bean != null){
			if(bean.getGameStatus().value()>=0){
				sql += " and game_status = " + bean.getGameStatus().value();
			}
			if(bean.getGameType()!=null){
				sql += " and game_type = " + bean.getGameType().value();
			}
			if(bean.getNetworkType().value()>0){
				sql += " and network_type = " + bean.getNetworkType().value();
			}
			if(!Utils.stringIsNullOrEmpty(bean.getGameName())){
				sql += "  and game_name like " + SqlUtils.QuataLikeStr(bean.getGameName().trim());
			}
			if(!Utils.stringIsNullOrEmpty(shortName)){
				sql += " and cp_id in ( SELECT cp_id from t_content_provider where short_name like " + SqlUtils.QuataLikeStr(shortName) + ")";
			}
		}
		
		System.out.println(sql);		
		return this.getInt(sql);
	}
	
	public String[] getFileNameAndPath(int appId,Long loginUserId,long efsId)throws Exception{
		String[] result = {"",""};
		FileInfo file = ManageClient.getInstance().getFileInfo(appId, loginUserId, efsId);
		if(file != null){
			String filepath =ConstVar.DOWNLOAD_URL + FileUtils.getFilePath(file.getFileUsedType(), efsId, file.getFileName());
			result[0] = file.getFileName()==null?"":file.getFileName();
			result[1] = filepath==null?"":filepath;				
		}
		return result;	
	}
	
	public String getLocalFilePath(int appId,Long loginUserId,long efsId)throws Exception{
		FileInfo file = ManageClient.getInstance().getFileInfo(appId, loginUserId, efsId);
		if(file != null){
			return FileUtils.getFilePath(file.getFileUsedType(), efsId, file.getFileName());
		}
		return "";
	}

	public String getFilePath(int appId,Long loginUserId,long efsId)throws Exception{
		FileInfo file = ManageClient.getInstance().getFileInfo(appId, loginUserId, efsId);
		if(file != null){
			String filepath =ConstVar.DOWNLOAD_URL + FileUtils.getFilePath(file.getFileUsedType(), efsId, file.getFileName());
			return filepath;		
		}
		return "";
	}	
	
	public ImageInfo getSmallImageInfoById(int appId,Long loginUserId,long efsId) throws Exception{
		ImageInfo image = null;
		FileInfo fileInfo = ManageClient.getInstance().getFileInfo(appId, loginUserId, efsId);		
		if(fileInfo != null && fileInfo.getFileUsedType() == FileUsedType.game_photo){
			String path = ConstVar.DOWNLOAD_URL + FileUtils.getFilePath(FileUsedType.game_logo_sml, efsId, fileInfo.getFileName());
			image = new ImageInfo();
			image.setId(fileInfo.getEFSId());				
			image.setName(fileInfo.getFileName().replaceAll("pic1", "pic2"));
			image.setPath(path);
			image.setWidth(ConstVar.GAME_PHOTO_LOG_SMALL_WIDTH);
			image.setHeight(ConstVar.GAME_PHOTO_LOG_SMALL_HEIGHT);
			image.setType(fileInfo.getImageType());	
			image.setSize(fileInfo.getFileSize());
			image.setFileUsedType(fileInfo.getFileUsedType());
			if(fileInfo.getFileUsedType()==FileUsedType.game_photo){				
				image.setRemark("游戏logo(详情页)");
			}	
		}		
		return image;
	}
	
	public ImageInfo getImageInfoById(int appId,Long loginUserId,long efsId) throws Exception{
		ImageInfo image = null;
		FileInfo fileInfo = ManageClient.getInstance().getFileInfo(appId, loginUserId, efsId);
		String path = this.getFilePath(appId, loginUserId, efsId);
		if(fileInfo != null){
			if(fileInfo.getFileUsedType()==FileUsedType.game_photo||fileInfo.getFileUsedType()==FileUsedType.game_view){
				image = new ImageInfo();
				
				image.setId(fileInfo.getEFSId());
				image.setName(fileInfo.getFileName());
				image.setPath(path);
				image.setWidth(ConstVar.GAME_PHOTO_VIEW_WIDTH);
				image.setHeight(ConstVar.GAME_PHOTO_VIEW_HEIGHT);
				image.setType(fileInfo.getImageType());	
				image.setSize(fileInfo.getFileSize());
				image.setFileUsedType(fileInfo.getFileUsedType());
				if(fileInfo.getFileUsedType()==FileUsedType.game_photo){				
					image.setRemark("游戏logo(列表页)");
					image.setWidth(ConstVar.GAME_PHOTO_LOG_BIG_WIDTH);
					image.setHeight(ConstVar.GAME_PHOTO_LOG_BIG_HEIGHT);
				}					
			}			
		}		
		return image;
	}
	
	public List<Integer> listGameIdsByGameStatus(List<Integer> ids, GameStatus status)throws ExceptionCommonBase{
		List<Integer> result = new ArrayList<Integer>();
		if(ids != null && ids.size()>0 && status != null){
			for(int id:ids){
				if(isGameExist(id,status)){
					result.add(id);
				}
			}
			return result;
		}
		return result;
	}
	
	public boolean isGameExist(int gid, GameStatus status)throws ExceptionCommonBase{
		String sql = "select count(*) from t_game WHERE g_id = " + gid;
		if(status != null && status != GameStatus.none){
			 sql += " and game_status= " + status.value();			
		}
		return this.getInt(sql)>0;
	}
	
	public int updateTCLAppId(int gid,String tclAppId)throws ExceptionCommonBase{
		if(gid>0 && !Utils.stringIsNullOrEmpty(tclAppId)){
			String sql = "update t_game set tcl_app_id = " + SqlUtils.QuataStr(tclAppId) + " where g_id = " + gid;
			return this.executeUpdate(sql);
		}
		return -1;
	}

	public String getTCLAppIdByPackageName(String entityPackageName) {
		StringBuilder xmlstr = new StringBuilder("<?xml version='1.0' encoding='utf-8'?>")
			.append("<existAppInfosRequest>")
			.append("<loginname>dianxinaiyouxi</loginname>")
			.append("<password>111111</password>")
			.append("<apkpkgname>").append(entityPackageName)
			.append("</apkpkgname>")
			.append("<apiversion>3.0</apiversion>")
			.append("</existAppInfosRequest>");
		byte[] xmlData = xmlstr.toString().getBytes();
		String tclAppId = null;
//		String urlstr = "http://118.194.161.121/service/appstore/game/getAppInfosByDevnameAndAppid";
		String urlstr = "http://interface.appstore.huan.tv/service/appstore/game/getAppInfosByDevnameAndAppid";
		try {
			URLConnection conn = new URL(urlstr).openConnection();
			conn.setDoOutput(true);
			conn.setDoInput(true);
			conn.setRequestProperty("Pragma:", "no-cache");
			conn.setRequestProperty("Cache-Control", "no-cache");
			conn.setRequestProperty("Content-Type", "application/xml");
			conn.setRequestProperty("Content-length", String.valueOf(xmlData.length));

			DataOutputStream printout = new DataOutputStream(conn.getOutputStream());
			printout.write(xmlData);
			printout.flush();
			printout.close();

			DocumentBuilder documentBuilder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
			Document doc = documentBuilder.parse(conn.getInputStream());
			Node node = doc.getElementsByTagName("appid").item(0);
			Node node2 = doc.getElementsByTagName("downloadcnt").item(0);
			if(node!=null){
				tclAppId=node.getFirstChild().getNodeValue();
			}
			String downloadcnt="";
			if(node2!=null){
				downloadcnt=node2.getFirstChild().getNodeValue();
				System.out.println("下载次数:"+downloadcnt);
			}
			System.out.println("appid:" + tclAppId);
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return Utils.stringIsNullOrEmpty(tclAppId)?"":tclAppId;
	}
	public List<Integer> listGameIds(GameInfo bean)throws ExceptionCommonBase{
		String sql = "select g_id  from t_game WHERE game_status = 6";
		if(bean != null){
			if(bean.getNetworkType() !=null && bean.getNetworkType().value()>0){
				sql += " and network_type = " + bean.getNetworkType().value();						
			}
			if(bean.getFeeCode()>0){
				sql += " and fee_code =  " + bean.getFeeCode();
			}
		}
		return this.getListInteger(sql);
	}
	
	public int updatePriceByGid(int gid,int price)throws ExceptionCommonBase{
		String sql = "update t_game set price = " + price + " where g_id = " + gid;
		return this.executeUpdate(sql);
	}

	public List<Integer> listGidByRandom()throws ExceptionCommonBase{
		String sql = "select g_id from t_game where game_status = " + GameStatus.business.value() + " ORDER BY RAND() limit 5";
		return this.getListInteger(sql);
	}

	public List<Integer> listGIdByName(int gid,String name,int status)throws ExceptionCommonBase{		
		String sql = "select g_id from t_game where 1=1";
		if(gid>0){
			sql += " and g_id = " + gid;
		}
		if(!Utils.stringIsNullOrEmpty(name)){
			sql +=" and game_name like " + SqlUtils.QuataLikeStr(name);
		}
		if(status>0){
			sql += " and game_status = " + status;
		}
		System.out.println(sql);		
		return this.getListInteger(sql);
	}
	
	public List<Integer> listGIdByTypeAndName(int gid,String name,int status,int gameType) throws ExceptionCommonBase{
		String sql = "select g_id from t_game where 1=1 and game_type=" + gameType;
		if(gid>0){
			sql += " and g_id = " + gid;
		}
		if(!Utils.stringIsNullOrEmpty(name)){
			sql +=" and game_name like " + SqlUtils.QuataLikeStr(name);
		}
		if(status>0){
			sql += " and game_status = " + status;
		}
		return this.getListInteger(sql);
	} 
	
	public String getCpCodeByGid(int gid)throws ExceptionCommonBase{
		String sql = "select cp_code from t_content_provider where cp_id = (select cp_id from t_game where g_id = " + gid + ")";
		String name = this.getString(sql);
		return name==null?"":name;
	}
	
}